﻿//TODO
//GetUrl : eliminare sezioni commentate
//Eccezioni : migliorare la gestione delle eccezioni speciallizando quelle con 'Exception'


using System;
using System.Data;
using System.Collections.Generic;
using System.Data.Entity.Validation;
using System.Linq;
using System.IO;
using System.Reflection;
using System.Text.RegularExpressions;
using System.Xml.Linq;
using HtmlAgilityPack;
using NFLManager.Data;
using HtmlDocument = HtmlAgilityPack.HtmlDocument;
using NFLManager.Properties;


namespace NFLManager
{

    public class DataManager
    {
        
        protected DataManager(){}

        protected static DataManager _dataManager = null;

        public static DataManager Istance
        {
            get { return _dataManager ?? (_dataManager = new DataManager()); }
        }
        
        #region utilities

        public DataTable CreateEmptyDataTable(Type myType, string title = "")
        {
            DataTable result = new DataTable(myType.Name);

            foreach (PropertyInfo info in myType.GetProperties())
            {
                result.Columns.Add(new DataColumn(info.Name, info.PropertyType));
            }

            return result;
        }

        public DataTable EmptyLeaders
        {
            get
            {
                return CreateEmptyDataTable(typeof(LEADERS));
                //DataTable result = new DataTable("EmptyPlayers");
                //result.Columns.Add("NAME", typeof(string));
                //result.Columns.Add("SURNAME", typeof(string));
                //result.Columns.Add("POS", typeof(string));

                //result.Columns.Add("WK", typeof(int));
                //result.Columns.Add("TEAM", typeof(string));
                //result.Columns.Add("OPP", typeof(string));
                //result.Columns.Add("AH", typeof(string));

                //result.Columns.Add("GAMES_G", typeof(int));
                //result.Columns.Add("GAMES_GS", typeof(int));

                //result.Columns.Add("PASSING_COMP", typeof(int));
                //result.Columns.Add("PASSING_ATT", typeof(int));
                //result.Columns.Add("PASSING_YDS", typeof(int));
                //result.Columns.Add("PASSING_TD", typeof(int));
                //result.Columns.Add("PASSING_INT", typeof(int));

                //result.Columns.Add("RUSHING_ATT", typeof(int));
                //result.Columns.Add("RUSHING_YDS", typeof(int));
                //result.Columns.Add("RUSHING_LNG", typeof(int));
                //result.Columns.Add("RUSHING_TD", typeof(int));

                //result.Columns.Add("RECEIVING_REC", typeof(int));
                //result.Columns.Add("RECEIVING_YDS", typeof(int));
                //result.Columns.Add("RECEIVING_LNG", typeof(int));
                //result.Columns.Add("RECEIVING_TD", typeof(int));

                //result.Columns.Add("FUMBLES_LOST", typeof(int));

                //result.Columns.Add("OVERALLFGS_LNG", typeof(int));
                //result.Columns.Add("OVERALLFGS_FGATT", typeof(int));
                //result.Columns.Add("OVERALLFGS_FGM", typeof(int));

                //result.Columns.Add("TACKLES_COMB", typeof(int));
                //result.Columns.Add("TACKLES_TOTAL", typeof(int));
                //result.Columns.Add("TACKLES_AST", typeof(int));
                //result.Columns.Add("TACKLES_SCK", typeof(int));
                //result.Columns.Add("TACKLES_SFTY", typeof(int));

                //result.Columns.Add("INTERCEPTIONS_PDEF", typeof(int));
                //result.Columns.Add("INTERCEPTIONS_INT", typeof(int));

                //result.Columns.Add("FUMBLES_FF", typeof(int));
            }
        }

        public DataTable EmptyMatchs
        {
            get
            {
                return CreateEmptyDataTable(typeof(MATCHS));
                //DataTable result = new DataTable("EmptyMatchs");

                //result.Columns.Add("WEEK", typeof(byte));
                //result.Columns.Add("AWAY", typeof(string));
                //result.Columns.Add("AWAY_SCORE", typeof(byte));
                //result.Columns.Add("HOME", typeof(string));
                //result.Columns.Add("HOME_SCORE", typeof(byte));
            }
        }

        public DataTable EmptyInjuries
        {
            get
            {
                return CreateEmptyDataTable(typeof(INJURIES));
                //DataTable result = new DataTable("EmptyInjuries");

                //result.Columns.Add("NAME", typeof(string));
                //result.Columns.Add("SURNAME", typeof(string));
                //result.Columns.Add("INJURY", typeof(string));
                //result.Columns.Add("POSITION", typeof(string));
                //result.Columns.Add("PRACTICE_STATUS", typeof(string));
                //result.Columns.Add("GAME_STATUS", typeof(string));
                //result.Columns.Add("TEAM", typeof(string));
            }
        }

        public DataTable EmptyTeams
        {
            get { return CreateEmptyDataTable(typeof(TEAMS)); }
        }

        public DataTable EmptyPlayers
        {
            get { return CreateEmptyDataTable(typeof(PLAYERS)); }
        }

        #endregion


        #region data_table

        public DataTable Teams()
        {
            var result = EmptyTeams;
            foreach (var team in Properties.Settings.TEAMS)
            {
                var row = result.NewRow();
                row["ID"] = team;
                row["NAME"] = NflDictionary.Istance.TeamIdtoDesc[team];
                row["CONFERENCE"] = NflDictionary.Istance.TeamIdtoConf[team];
                row["DIVISION"] = NflDictionary.Istance.TeamIdtoDiv[team];
                row["OFF_SCHEMA"] = "";
                row["QB_SCHEMA"] = "";
                row["DEF_SCHEMA"] = "";
                row["TYPE_OFF"] = "";
                row["TYPE_DEF"] = "";
                result.Rows.Add(row);
            }
            return result;
        }

        public DataTable Matchs(string season, byte palyedMatchs = 17)
        {
            var result = EmptyMatchs;
            for (int i = 1; i <= palyedMatchs; i++) result.Merge(Matchs(i, season));
            return result;
        }

        public DataTable Players()
        {
            var teams = GetTeamsFromDB();
            var result = EmptyPlayers;
            foreach (var team in teams)
            {
                result.Merge(Players(team));
            }
            return result;
        }

        public DataTable Injuries(byte palyedMatchs = 17)
        {
            var result = EmptyInjuries;
            for (int i = 1; i <= 17; i++)
            {
                var dt = Injuries(i);
                if(dt != null) result.Merge(dt);
            }
            return result;
        }

        public DataTable Leaders()
        {
            var result = EmptyLeaders;
            var urls = GetUrlsFromPlayersTableDB();
            Controller.Istance.ActivateProgress(urls.Count);
            foreach (var url in urls)
            {
                try
                {
                    result.Merge(GetLeaderDataTable(url));
                    Controller.Istance.AddProgress();
                }
                catch (System.Data.DataException de)
                {
                    Logs.Istance.LogException("DataManager.Leaders", url, de);
                }
            }
            return result;    

        }

        public DataTable Matchs(int week, string season)
        {
            
            string awayId;
            string homeId;
            string awayName;
            string homeName;

            var entries = new List<string>();
            entries.Add("vuota");
            
            var result = EmptyMatchs;

            //Fetch.Istance._doc = Fetch._web.Load(GetUrl("schedule", week: week.ToString(), season: season));
            //var matchs = Fetch._doc.DocumentNode.SelectNodes("//div").ToList().Where(x => x.GetAttributeValue("class", "").Equals("list-matchup-row-team"));

            //foreach (var m in matchs)
            //{
            
            //    awayName = m.Descendants().First(s => s.GetAttributeValue("class", "").Contains("team-name away")).InnerText;
            //    awayId = NflDictionary.Istance.TeamIdtoDesc.ToList().First(v => v.Value.Contains(awayName.ToLower())).Key;
            //    homeName = m.Descendants().First(s => s.GetAttributeValue("class", "").Contains("team-name home")).InnerText;
            //    homeId = NflDictionary.Istance.TeamIdtoDesc.ToList().First(v => v.Value.Contains(homeName.ToLower())).Key;

            //    if(!entries.Contains(awayId))
            //    {
            //        var matchRow = result.NewRow();
                    
            //        matchRow["WEEK"] = week;
            //        matchRow["AWAY"] = awayId;
            //        matchRow["HOME"] = homeId;
            //        if (m.Descendants().Any(s => s.GetAttributeValue("class", "").Contains("team-score away")))
            //            matchRow["AWAY_SCORE"] = byte.Parse(m.Descendants().First(s => s.GetAttributeValue("class", "").Contains("team-score away")).InnerText);
            //        if (m.Descendants().Any(s => s.GetAttributeValue("class", "").Contains("team-score home")))
            //            matchRow["HOME_SCORE"] = byte.Parse(m.Descendants().First(s => s.GetAttributeValue("class", "").Contains("team-score home")).InnerText);
                    
            //        result.Rows.Add(matchRow);
            //        entries.Add(awayId);
            //    }
            //}
            
            return result;
        }

        private static HtmlNode GetInjuriesTable(int week)
        {
            int i = 0;

            //preleva le tabelle da modificare
            _doc = _web.Load(GetUrl("injuries", week.ToString()));
            var oldTables = _doc.DocumentNode.Descendants("div").FirstOrDefault(d => d.GetAttributeValue("class", "").Contains("InjuriesTable")).Descendants("table");

            //crea la nuova tabella
            HtmlNode result = HtmlNode.CreateNode("<table></table>");
            HtmlNode htmlTitleRow = HtmlNode.CreateNode("<tr colspan=\"7\"></tr>");

            //modifica le intestazioni
            string r0c1 = "<td>NAME</td>";
            string r0c2 = "<td>SURNAME</td>";
            string r0c3 = "<td>TEAM</td>";

            i = 0;
            foreach (var col in oldTables.ElementAt(1).Descendants("tr").ElementAt(1).Descendants("td"))
            {
                if (i == 0)
                {
                    htmlTitleRow.AppendChild(HtmlNode.CreateNode(r0c1));
                    htmlTitleRow.AppendChild(HtmlNode.CreateNode(r0c2));
                    htmlTitleRow.AppendChild(HtmlNode.CreateNode(r0c3));
                    i++;
                }
                else
                {
                    var htmlCol = string.Format("<td>{0}</td>", CleanValue(col.InnerText));
                    htmlTitleRow.AppendChild(HtmlNode.CreateNode(htmlCol));
                    i++;
                }
            }
            result.AppendChild(htmlTitleRow);

            //ricostruisce la tabella a partire dal set di tabelle
            foreach (var oldTable in oldTables)
            {
                var teamName = oldTable.Descendants("tr").ElementAt(2).Descendants("td").ElementAt(0).InnerText;
                var rows = oldTable.Descendants("tr").Skip(3);

                //ignora se non ci sono report sugli infortuni
                foreach (var row in rows.Where(r => r.Descendants("td").ElementAt(0).InnerText != "No injuries to report."))
                {
                    HtmlNode htmlRow = HtmlNode.CreateNode("<tr></tr>");

                    if (row.Descendants("td").ElementAt(0).GetAttributeValue("colspan", "") == "5")
                        teamName = row.Descendants("td").ElementAt(0).InnerText;
                    else
                    {
                        i = 0;
                        foreach (var col in row.Descendants("td"))
                        {
                            if (i == 0)
                            {
                                var htmlCol1 = string.Format("<td>{0}</td>", CleanValue(col.InnerText.Split(' ')[0]));
                                htmlRow.AppendChild(HtmlNode.CreateNode(htmlCol1));
                                var htmlCol2 = string.Format("<td>{0}</td>", CleanValue(col.InnerText.Split(' ')[1]));
                                htmlRow.AppendChild(HtmlNode.CreateNode(htmlCol2));
                                var htmlCol3 = string.Format("<td>{0}</td>", teamName);
                                htmlRow.AppendChild(HtmlNode.CreateNode(htmlCol3));
                                i++;
                            }
                            else
                            {
                                var htmlCol = string.Format("<td>{0}</td>", CleanValue(col.InnerText));
                                htmlRow.AppendChild(HtmlNode.CreateNode(htmlCol));
                                i++;
                            }
                        }
                        result.AppendChild(htmlRow);
                    }
                }
            }

            return result;
        }

        public DataTable Players(string team, HtmlNode nodes)
        {
            
            var result = GetDataTableFromHtmlTable("PLAYERS", table, 1,GetUrl("roster", team: team));
            DataColumn newColumn = new DataColumn("TEAM", typeof(string));
            newColumn.DefaultValue = team;
            result.Columns.Add(newColumn);
            return result;
        }

        private void Injuries(bool findDataAwayJson, string dataAwayJson, string away, bool findDataHomeJson,
                                          string dataHomeJson, string home)
        {
            DataTable result;
            if (findDataAwayJson)
            {
                var awayInjuryPlayers = dataAwayJson.Split(';');
                foreach (var awayInjuryPlayer in awayInjuryPlayers)
                {
                    var injuryData = awayInjuryPlayer.Split(',');
                    var row = result.NewRow();
                    row["NAME"] = injuryData[6].Split(':')[1].Replace("\"", "");
                    row["SURNAME"] = injuryData[5].Split(':')[1].Replace("\"", "");
                    row["INJURY"] = injuryData[2].Split(':')[1].Replace("\"", "");
                    row["POSITION"] = injuryData[1].Split(':')[1].Replace("\"", "");
                    row["PRACTICESTATUS"] = injuryData[3].Split(':')[1].Replace("\"", "");
                    row["GAMESTATUS"] = injuryData[4].Split(':')[1].Replace("\"", "");
                    row["TEAM"] = away;
                    result.Rows.Add(row);
                }
            }

            if (findDataHomeJson)
            {
                var homeInjuryPlayers = dataHomeJson.Split(';');
                foreach (var homeInjuryPlayer in homeInjuryPlayers)
                {
                    var injuryData = homeInjuryPlayer.Split(',');
                    var row = result.NewRow();
                    row["NAME"] = injuryData[6].Split(':')[1].Replace("\"", "");
                    row["SURNAME"] = injuryData[5].Split(':')[1].Replace("\"", "");
                    row["INJURY"] = injuryData[2].Split(':')[1].Replace("\"", "");
                    row["POSITION"] = injuryData[1].Split(':')[1].Replace("\"", "");
                    row["PRACTICESTATUS"] = injuryData[3].Split(':')[1].Replace("\"", "");
                    row["GAMESTATUS"] = injuryData[4].Split(':')[1].Replace("\"", "");
                    row["TEAM"] = home;
                    result.Rows.Add(row);
                }
            }
        }

        public DataTable Injuries(int week)
        {
            DataTable result = null;
            Fetch._doc = Fetch._web.Load(GetUrl("injuries", week.ToString()));
            var scripts = Fetch._doc.DocumentNode.SelectNodes("//script[contains(text(), 'dataAway')]");
            result = Fetch.GetInstance().GetInjuries(scripts);
            DataColumn newColumn = new DataColumn("WEEK", typeof(byte));
            newColumn.DefaultValue = week;
            result.Columns.Add(newColumn);
            return result;
        }

        public DataTable GetLeaderDataTable(string url)
        {
            DataTable result = null;
            try
            {
                var recentGamesTable = Fetch.GetInstance().GetPlayerRecentGames(url);
                if (recentGamesTable.Descendants("tr")
                                    .ElementAt(1).Descendants("td")
                                    .ElementAt(0).InnerText.Contains("This player does not have any statistics..."))
                {
                    return EmptyLeaders;
                }


                string urlComp = null;
                var urlSplits = url.Split('/');
                urlComp = Settings.Default.URL_NFL
                          + "/" + urlSplits[3]
                          + "/" + urlSplits[4]
                          + "/" + urlSplits[5]
                          + "/gamelogs";

                var table = Fetch.GetInstance().GetPlayerRegularSeason(urlComp);
                result = GetDataTableFromHtmlTable("LEADERS", table, 2, urlComp);
                var nameColumn = new DataColumn("NAME", typeof(string));
                nameColumn.DefaultValue = GetPlayerNameFromPlayersTableDB(url);
                result.Columns.Add(nameColumn);
                DataColumn surnameColumn = new DataColumn("SURNAME", typeof(string));
                surnameColumn.DefaultValue = GetPlayerSurNameFromPlayersTableDB(url);
                result.Columns.Add(surnameColumn);
                DataColumn roleColumn = new DataColumn("ROLE", typeof(string));
                roleColumn.DefaultValue = GetPlayerRoleFromPlayersTableDB(url);
                result.Columns.Add(roleColumn);
            }
            catch(System.InvalidOperationException)
            {
                result = EmptyLeaders;
            }
            return result;
        }

        #endregion
        
        #region DB

        public static void WriteTeamsTableToDB(DataTable dt)
        {
            
            using (var context = new NflDbEntities())
            {
                foreach (DataRow row in dt.Rows)
                {
                    var team = new TEAMS()
                    {
                        ID = row["ID"].ToString(),
                        NAME = row["NAME"].ToString(),
                        CONFERENCE = row["CONFERENCE"].ToString(),
                        DIVISION = row["DIVISION"].ToString(),
                        OFF_SCHEMA = row["OFF_SCHEMA"].ToString(),
                        QB_SCHEMA = row["QB_SCHEMA"].ToString(),
                        DEF_SCHEMA = row["DEF_SCHEMA"].ToString(),
                        TYPE_OFF = row["TYPE_OFF"].ToString(),
                        TYPE_DEF = row["TYPE_DEF"].ToString(),
                    };
                    context.TEAMS.Add(team);
                    
                }
                context.SaveChanges();
            }
        }

        public static void WriteMatchsTableToDB(DataTable dt)
        {
            
            using (var context = new NflDbEntities())
            {
                foreach (DataRow row in dt.Rows)
                {
                    var match = new MATCHS()
                    {
                        
                        WEEK  = byte.Parse(row["WEEK"].ToString()),
                        AWAY = row["AWAY"].ToString(),
                        AWAY_SCORE = byte.Parse(row["AWAY_SCORE"].ToString()),
                        HOME = row["HOME"].ToString(),
                        HOME_SCORE = byte.Parse(row["HOME_SCORE"].ToString())
                    };
                    context.MATCHS.Add(match);

                }
                context.SaveChanges();
            }
            
        }

        public static void WritePlayersTableToDB(DataTable dt)
        {
            using (var context = new NflDbEntities())
            {
                foreach (DataRow row in dt.Rows)
                {
                    var player = new PLAYERS
                    {

                        TEAM = row["TEAM"].ToString(),
                        NO = Int32.Parse(row["NO"].ToString()),
                        NAME = row["NAME"].ToString(),
                        SURNAME = row["SURNAME"].ToString(),
                        POS = row["POS"].ToString(),
                        STATUS = row["STATUS"].ToString(),
                        HEIGHT = row["HEIGHT"].ToString(),
                        WEIGHT = Int32.Parse(row["WEIGHT"].ToString()),
                        BIRTHDATE = row["BIRTHDATE"].ToString(),
                        EXP = Int32.Parse(row["EXP"].ToString()),
                        COLLEGE = row["COLLEGE"].ToString(),
                        REF = row["REF"].ToString()
                    };
                    context.PLAYERS.Add(player);

                }
                try
                {
                    context.SaveChanges();
                }
                

                catch (DbEntityValidationException z)
                {
                    foreach (var eve in z.EntityValidationErrors)
                    {
                        System.Diagnostics.Debug.WriteLine("Entity of type \"{0}\" in state \"{1}\" has the following validation errors:",
                            eve.Entry.Entity.GetType().Name, eve.Entry.State);
                        foreach (var ve in eve.ValidationErrors)
                        {
                            System.Diagnostics.Debug.WriteLine("- Property: \"{0}\", Error: \"{1}\"",
                                ve.PropertyName, ve.ErrorMessage);
                        }
                    }
                    throw;
                }
            }
        }

        public static void WriteInjuriesTableToDB(DataTable dt)
        {
            using (var context = new NflDbEntities())
            {
                foreach (DataRow row in dt.Rows)
                {
                    var injury = new INJURIES
                    {
                        WEEK  = byte.Parse(row["WEEK"].ToString()),
                        TEAM = row["TEAM"].ToString(),
                        NAME = row["NAME"].ToString(),
                        SURNAME = row["SURNAME"].ToString(),
                        POSITION = row["POSITION"].ToString(),
                        INJURY = row["INJURY"].ToString(),
                        PRACTICESTATUS = row["PRACTICESTATUS"].ToString(),
                        GAMESTATUS = row["GAMESTATUS"].ToString()
                    };
                    context.INJURIES.Add(injury);

                }
                try
                {
                    context.SaveChanges();
                }


                catch (DbEntityValidationException z)
                {
                    foreach (var eve in z.EntityValidationErrors)
                    {
                        System.Diagnostics.Debug.WriteLine("Entity of type \"{0}\" in state \"{1}\" has the following validation errors:",
                            eve.Entry.Entity.GetType().Name, eve.Entry.State);
                        foreach (var ve in eve.ValidationErrors)
                        {
                            System.Diagnostics.Debug.WriteLine("- Property: \"{0}\", Error: \"{1}\"",
                                ve.PropertyName, ve.ErrorMessage);
                        }
                    }
                    throw;
                }
            }
            
        }

        public static void WriteLeadersTableToDB(DataTable dt)
        {
            using (var context = new NflDbEntities())
            {
                foreach (DataRow row in dt.Rows)
                {
                    var leader = new LEADERS
                    {
                        NAME = row["NAME"].ToString(),
                        SURNAME = row["SURNAME"].ToString(),
                        ROLE = row["ROLE"].ToString(),
                        WK = byte.Parse(row["WK"].ToString()),
                        TEAM = row["TEAM"].ToString(),
                        OPP = row["OPP"].ToString(),
                        AH = row["AH"].ToString(),
                        GAMES_G = byte.Parse(row["GAMES_G"].ToString()),
                        GAMES_GS = byte.Parse(row["GAMES_GS"].ToString()),
                        PASSING_COMP = byte.Parse(row["PASSING_COMP"].ToString()),
                        PASSING_ATT = byte.Parse(row["PASSING_ATT"].ToString()),
                        PASSING_YDS = int.Parse(row["PASSING_YDS"].ToString()),
                        PASSING_TD = byte.Parse(row["PASSING_TD"].ToString()),
                        PASSING_INT = byte.Parse(row["PASSING_INT"].ToString()),
                        RUSHING_ATT = byte.Parse(row["RUSHING_ATT"].ToString()),
                        RUSHING_YDS = int.Parse(row["RUSHING_YDS"].ToString()),
                        RUSHING_LNG = byte.Parse(row["RUSHING_LNG"].ToString()),
                        RUSHING_TD = byte.Parse(row["RUSHING_TD"].ToString()),
                        RECEIVING_REC = byte.Parse(row["RECEIVING_REC"].ToString()),
                        RECEIVING_YDS = int.Parse(row["RECEIVING_YDS"].ToString()),
                        RECEIVING_LNG = byte.Parse(row["RECEIVING_LNG"].ToString()),
                        RECEIVING_TD = byte.Parse(row["RECEIVING_TD"].ToString()),
                        FUMBLES_LOST = byte.Parse(row["FUMBLES_LOST"].ToString()),
                        OVERALLFGS_LNG = byte.Parse(row["OVERALLFGS_LNG"].ToString()),
                        OVERALLFGS_FGATT = byte.Parse(row["OVERALLFGS_FGATT"].ToString()),
                        OVERALLFGS_FGM = byte.Parse(row["OVERALLFGS_FGM"].ToString()),
                        TACKLES_COMB = byte.Parse(row["TACKLES_COMB"].ToString()),
                        TACKLES_TOTAL = byte.Parse(row["TACKLES_TOTAL"].ToString()),
                        TACKLES_AST = byte.Parse(row["TACKLES_AST"].ToString()),
                        TACKLES_SCK = byte.Parse(row["TACKLES_SCK"].ToString()),
                        TACKLES_SFTY = byte.Parse(row["TACKLES_SFTY"].ToString()),
                        INTERCEPTIONS_PDEF = byte.Parse(row["INTERCEPTIONS_PDEF"].ToString()),
                        INTERCEPTIONS_INT = byte.Parse(row["INTERCEPTIONS_INT"].ToString()),
                        FUMBLES_FF = byte.Parse(row["FUMBLES_FF"].ToString())
                    };
                    context.LEADERS.Add(leader);

                }
                try
                {
                    context.SaveChanges();
                }


                catch (DbEntityValidationException z)
                {
                    foreach (var eve in z.EntityValidationErrors)
                    {
                        System.Diagnostics.Debug.WriteLine("Entity of type \"{0}\" in state \"{1}\" has the following validation errors:",
                            eve.Entry.Entity.GetType().Name, eve.Entry.State);
                        foreach (var ve in eve.ValidationErrors)
                        {
                            System.Diagnostics.Debug.WriteLine("- Property: \"{0}\", Error: \"{1}\"",
                                ve.PropertyName, ve.ErrorMessage);
                        }
                    }
                    throw;
                }
            }
            
        }
        
        public List<string> GetTeamsFromDB()
        {
            
            List<string> result;
            using (var context = new NflDbEntities())
            {
                var query = from team in context.TEAMS
                            select team.ID;
                result = query.ToList();
            }
            return result;

        }

        public static List<string> GetUrlsFromPlayersTableDB()
        {

            List<string> result;
            using (var context = new NflDbEntities())
            {
                var query = from player in context.PLAYERS
                            select player.REF;
                result = query.ToList();
            }
            return result;

        }

        public static string GetPlayerNameFromPlayersTableDB(string url)
        {

            string result;
            using (var context = new NflDbEntities())
            {
                var query = from player in context.PLAYERS
                            where player.REF == url
                            select player.NAME;
                result = query.First();
            }
            return result;

        }

        public static string GetPlayerRoleFromPlayersTableDB(string url)
        {

            string result;
            using (var context = new NflDbEntities())
            {
                var query = from player in context.PLAYERS
                            where player.REF == url
                            select player.POS;
                result = query.First();
            }
            return result;

        }

        public static string GetPlayerSurNameFromPlayersTableDB(string url)
        {

            string result;
            using (var context = new NflDbEntities())
            {
                var query = from player in context.PLAYERS
                            where player.REF == url
                            select player.SURNAME;
                result = query.First();
            }
            return result;

        }

        #endregion

    }
}
